package com.cssy.yd.logic.web;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RequestMapping;

import com.cssy.base.web.BaseController;
import com.cssy.yd.logic.entity.PoItemExecuteReportFormsDO;
import com.cssy.yd.logic.entity.PoItemReportFormsDO;
import com.cssy.yd.logic.service.ILogicPoService;
import com.plat.helper.JqGridHandler;
import com.plat.helper.SearchConditions;
import com.plat.helper.SearchResult;
import com.plat.util.DateUtils;
import com.plat.util.json.OutJsonGrid;

@Controller
@RequestMapping("/yd/logic/poItemExecuteReportForms")
public class PoItemExecuteReportFormsController extends BaseController<PoItemExecuteReportFormsController>{
	private static final String MANAGER = "/yd/logic/poItemExecuteReportForms/PoItemExecuteManager";
	
	@Resource(name="logicPoService")
	private ILogicPoService logicPoService;
	
	@Override
	public String manager(HttpServletRequest request, Model model)
			throws Exception {
		// TODO Auto-generated method stub
		return MANAGER;
	}

	@Override
	public void list(HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		JqGridHandler hander = new JqGridHandler(request);
		SearchConditions scs = new SearchConditions();
		hander.getWheres(scs);
		String wlCode = hander.getQueryValue("wlCode");
		String wlName = hander.getQueryValue("wlName");
		String gysCode = hander.getQueryValue("gysCode");
		String gysName = hander.getQueryValue("gysName");
		String ddCode = hander.getQueryValue("ddCode");
		String rkCode = hander.getQueryValue("rkCode");
		Map<String, Object> map = new HashMap<String, Object>();
		if(!"".equals(wlCode)){
			map.put("wlCode", wlCode);
		}
		if(!"".equals(wlName)){
			map.put("wlName", wlName);
		}
		if(!"".equals(gysCode)){
			map.put("gysCode", gysCode);
		}
		if(!"".equals(gysName)){
			map.put("gysName", gysName);
		}
		if(!"".equals(ddCode)){
			map.put("ddCode", ddCode);
		}
		if(!"".equals(rkCode)){
			map.put("rkCode", rkCode);
		}
		map.put("limitClause", scs.getLimitClause());
		SearchResult result = logicPoService.findPoItemExecuteByExample(map);
		OutJsonGrid out = new OutJsonGrid(result);
		out.printJSon(response);
	}

	@Override
	public String operationForm(HttpServletRequest request, String id,
			String flag, Model model) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<String, Object> save(HttpServletRequest request,
			PoItemExecuteReportFormsController curAP, BindingResult result,
			Model model) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<String, Object> delete(HttpServletRequest request,
			HttpServletResponse response, Model model) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	/**
	 * 采购明细执行情况报表
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping("/exportExcelPoItemExecute")
	public void exportExcelPoItem(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		JqGridHandler hander = new JqGridHandler(request);
		SearchConditions scs = new SearchConditions();
		hander.setFilters(new String(request.getParameter("filters").getBytes(
				"ISO-8859-1"), "UTF-8"));
		hander.getWheres(scs);
		String wlCode = hander.getQueryValue("wlCode");
		String wlName = hander.getQueryValue("wlName");
		String gysCode = hander.getQueryValue("gysCode");
		String gysName = hander.getQueryValue("gysName");
		String ddCode = hander.getQueryValue("ddCode");
		String rkCode = hander.getQueryValue("rkCode");
		Map<String, Object> map = new HashMap<String, Object>();
		if(!"".equals(wlCode)){
			map.put("wlCode", wlCode);
		}
		if(!"".equals(wlName)){
			map.put("wlName", wlName);
		}
		if(!"".equals(gysCode)){
			map.put("gysCode", gysCode);
		}
		if(!"".equals(gysName)){
			map.put("gysName", gysName);
		}
		if(!"".equals(ddCode)){
			map.put("ddCode", ddCode);
		}
		if(!"".equals(rkCode)){
			map.put("rkCode", rkCode);
		}
		//map.put("limitClause", scs.getLimitClause());
		SearchResult result = logicPoService.findPoItemExecuteByExample(map);
		List<PoItemExecuteReportFormsDO> list = result.getResults();
		HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象  
		HSSFSheet sheet = wb.createSheet("new sheet");//建立新的sheet对象  
		if(list.size()>0){
			HSSFCellStyle style = wb.createCellStyle();      
			style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框       
			style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框       
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框       
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
			style.setBorderBottom((short) 1);
			style.setBorderLeft((short) 1);
			style.setBorderRight((short) 1);
			style.setBorderTop((short) 1);
			style.setAlignment((short) 2);
			style.setVerticalAlignment((short) 1l);
			
			
			HSSFCellStyle headerStyle = wb.createCellStyle();
			headerStyle.setAlignment((short) 2);
			headerStyle.setAlignment((short) 2);
			headerStyle.setBorderBottom((short) 1);
			headerStyle.setBorderLeft((short) 1);
			headerStyle.setBorderRight((short) 1);
			headerStyle.setBorderTop((short) 1);
			headerStyle.setVerticalAlignment((short) 1);
			HSSFFont bold1 = wb.createFont();
			bold1.setFontName("黑体");
			bold1.setFontHeightInPoints((short) 12);
			bold1.setBoldweight((short) 50);
			headerStyle.setFont(bold1);
			headerStyle.setFillPattern((short) 1);
			headerStyle.setFillForegroundColor((short) 43);
			
			
			HSSFRow row4 = sheet.createRow((short)0);//建立新行
			Region region = new Region(0,(short) 0, 1, (short) 0);
			Region region1 = new Region(0,(short) 1, 1, (short) 1);
			Region region2 = new Region(0,(short) 2, 1, (short) 2);
			Region region3 = new Region(0,(short) 3, 1, (short) 3);
			Region region4 = new Region(0,(short) 4, 1, (short) 4);
			Region region5 = new Region(0,(short) 5, 1, (short) 5);
			Region region6 = new Region(0,(short) 6, 1, (short) 6);
			Region region7 = new Region(0,(short) 7, 0, (short) 10);
			Region region11 = new Region(0,(short) 11, 1, (short) 11);
			Region region8 = new Region(0,(short) 12, 0, (short) 15);
			Region region9 = new Region(0,(short) 16, 0, (short) 17);
			Region region10 = new Region(0,(short) 18, 1, (short) 18);
			sheet.addMergedRegion(region);
			sheet.addMergedRegion(region1);
			sheet.addMergedRegion(region2);
			sheet.addMergedRegion(region3);
			sheet.addMergedRegion(region4);
			sheet.addMergedRegion(region5);
			sheet.addMergedRegion(region6);
			sheet.addMergedRegion(region7);
			sheet.addMergedRegion(region8);
			sheet.addMergedRegion(region9);
			sheet.addMergedRegion(region10);
			sheet.addMergedRegion(region11);
			
			HSSFCell cell1 = row4.createCell((short)0);
			cell1.setCellValue("订单编号");
			cell1.setCellStyle(headerStyle);
			HSSFCell cell2 = row4.createCell((short)1);
			cell2.setCellValue("物料编码");
			cell2.setCellStyle(headerStyle);
			HSSFCell cell3 = row4.createCell((short)2);
			cell3.setCellValue("物料名称");
			cell3.setCellStyle(headerStyle);
			HSSFCell cell4 = row4.createCell((short)3);
			cell4.setCellValue("规格型号");
			cell4.setCellStyle(headerStyle);
			HSSFCell cell5 = row4.createCell((short)4);
			cell5.setCellValue("供应商编码");
			cell5.setCellStyle(headerStyle);
			HSSFCell cell6 = row4.createCell((short)5);
			cell6.setCellValue("供应商名称");
			cell6.setCellStyle(headerStyle);
			HSSFCell cell7 = row4.createCell((short)6);
			cell7.setCellValue("收货日期");
			cell7.setCellStyle(headerStyle);
			HSSFCell cell8 = row4.createCell((short)7);
			cell8.setCellValue("订单");
			cell8.setCellStyle(headerStyle);
			HSSFCell cell9 = row4.createCell((short)11);
			cell9.setCellValue("入库单号");
			cell9.setCellStyle(headerStyle);
			HSSFCell cell10 = row4.createCell((short)12);
			cell10.setCellValue("入库");
			cell10.setCellStyle(headerStyle);
			HSSFCell cell11 = row4.createCell((short)16);
			cell11.setCellValue("未入库");
			cell11.setCellStyle(headerStyle);
			HSSFCell cell12 = row4.createCell((short)18);
			cell12.setCellValue("部门");
			cell12.setCellStyle(headerStyle);
			
			
			
			HSSFRow row5 = sheet.createRow((short)1);//建立新行
			HSSFCell c11 = row5.createCell((short)0);
			c11.setCellStyle(headerStyle);
			HSSFCell c12 = row5.createCell((short)1);
			c12.setCellStyle(headerStyle);
			HSSFCell c13 = row5.createCell((short)2);
			c13.setCellStyle(headerStyle);
			HSSFCell c14 = row5.createCell((short)3);
			c14.setCellStyle(headerStyle);
			HSSFCell c15 = row5.createCell((short)4);
			c15.setCellStyle(headerStyle);
			HSSFCell c16 = row5.createCell((short)5);
			c16.setCellStyle(headerStyle);
			HSSFCell c17 = row5.createCell((short)6);
			c17.setCellStyle(headerStyle);
			
			HSSFCell c18 = row5.createCell((short)7);
			c18.setCellValue("订单基本单位");
			c18.setCellStyle(headerStyle);
			HSSFCell c19 = row5.createCell((short)8);
			c19.setCellValue("订单基本数量");
			c19.setCellStyle(headerStyle);
			HSSFCell c20 = row5.createCell((short)9);
			c20.setCellValue("订单正常单位");
			c20.setCellStyle(headerStyle);
			HSSFCell c21 = row5.createCell((short)10);
			c21.setCellValue("订单正常数量");
			c21.setCellStyle(headerStyle);
			HSSFCell c26 = row5.createCell((short)11);
			c26.setCellStyle(headerStyle);
			HSSFCell c22 = row5.createCell((short)12);
			c22.setCellValue("入库基本单位");
			c22.setCellStyle(headerStyle);
			HSSFCell c23 = row5.createCell((short)13);
			c23.setCellValue("入库基本数量");
			c23.setCellStyle(headerStyle);
			HSSFCell c24 = row5.createCell((short)14);
			c24.setCellValue("入库正常单位");
			c24.setCellStyle(headerStyle);
			HSSFCell c25 = row5.createCell((short)15);
			c25.setCellValue("入库正常数量");
			c25.setCellStyle(headerStyle);
			HSSFCell c27 = row5.createCell((short)16);
			c27.setCellValue("未入库基本数量");
			c27.setCellStyle(headerStyle);
			HSSFCell c28 = row5.createCell((short)17);
			c28.setCellValue("未入库正常数量");
			c28.setCellStyle(headerStyle);
			HSSFCell c29 = row5.createCell((short)18);
			c29.setCellValue("部门");
			c29.setCellStyle(headerStyle);
				for(int i=0;i<list.size();i++){
					HSSFRow row6 = sheet.createRow((short)(2+i));//建立新行  
					sheet.setColumnWidth(0, 5000);
					HSSFCell c31 = row6.createCell((short)0);
					c31.setCellStyle(style);
					c31.setCellValue(list.get(i).getDdCode());
					sheet.setColumnWidth(1, 5000);
					HSSFCell c32 = row6.createCell((short)1);
					c32.setCellStyle(style);
					c32.setCellValue(list.get(i).getWlCode());
					sheet.setColumnWidth(2, 5000);
					HSSFCell c33 = row6.createCell((short)2);
					c33.setCellStyle(style);
					c33.setCellValue(list.get(i).getWlName());
					sheet.setColumnWidth(3, 10000);
					HSSFCell c34 = row6.createCell((short)3);
					c34.setCellStyle(style);
					c34.setCellValue(list.get(i).getSpec());
					sheet.setColumnWidth(4, 5000);
					HSSFCell c35 = row6.createCell((short)4);
					c35.setCellStyle(style);
					c35.setCellValue(list.get(i).getGysCode());
					sheet.setColumnWidth(5, 5000);
					HSSFCell c36 = row6.createCell((short)5);
					c36.setCellStyle(style);
					c36.setCellValue(list.get(i).getGysName());
					sheet.setColumnWidth(6, 5000);
					HSSFCell c38 = row6.createCell((short)6);
					c38.setCellStyle(style);
					if(list.get(i).getShrq() != null){
						c38.setCellValue(DateUtils.dateToString(list.get(i).getShrq(),"yyyy-MM-dd"));
					}else{
						c38.setCellValue("");
					}
					sheet.setColumnWidth(7, 5000);
					HSSFCell c39 = row6.createCell((short)7);
					c39.setCellStyle(style);
					c39.setCellValue(list.get(i).getDdJunit());
					sheet.setColumnWidth(8, 5000);
					HSSFCell c40 = row6.createCell((short)8);
					c40.setCellStyle(style);
					c40.setCellValue(list.get(i).getDdJquantity());
					sheet.setColumnWidth(9, 5000);
					HSSFCell c41 = row6.createCell((short)9);
					c41.setCellStyle(style);
					c41.setCellValue(list.get(i).getDdUnit());
					sheet.setColumnWidth(10, 5000);
					HSSFCell c42 = row6.createCell((short)10);
					c42.setCellStyle(style);
					c42.setCellValue(list.get(i).getDdQuantity());
					sheet.setColumnWidth(11, 5000);
					HSSFCell c88 = row6.createCell((short)11);
					c88.setCellStyle(style);
					c88.setCellValue(list.get(i).getRkCode());
					sheet.setColumnWidth(12, 5000);
					HSSFCell c43 = row6.createCell((short)12);
					c43.setCellStyle(style);
					c43.setCellValue(list.get(i).getRkJunit());
					sheet.setColumnWidth(13, 5000);
					HSSFCell c44 = row6.createCell((short)13);
					c44.setCellStyle(style);
					c44.setCellValue(list.get(i).getRkJquantity());
					sheet.setColumnWidth(14, 5000);
					HSSFCell c45 = row6.createCell((short)14);
					c45.setCellStyle(style);
					c45.setCellValue(list.get(i).getRkUnit());
					sheet.setColumnWidth(15, 5000);
					HSSFCell c46 = row6.createCell((short)15);
					c46.setCellStyle(style);
					c46.setCellValue(list.get(i).getRkQuantity());
					sheet.setColumnWidth(16, 5000);
					HSSFCell c47 = row6.createCell((short)16);
					c47.setCellStyle(style);
					c47.setCellValue(list.get(i).getWrkJquantity());
					sheet.setColumnWidth(17, 5000);
					HSSFCell c48 = row6.createCell((short)17);
					c48.setCellStyle(style);
					c48.setCellValue(list.get(i).getWrkQuantity());
					HSSFCell c49 = row6.createCell((short)18);
					c49.setCellStyle(style);
					c49.setCellValue(list.get(i).getDepartment());
				}
			}
			response.setContentType("text/html;charset=UTF-8");
			response.reset();
			response.setContentType("application/ms-excel");
			String fileNameDisplay = "采购明细执行情况报表"+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
			try {
				fileNameDisplay = new String(fileNameDisplay.getBytes("GBK"),
						"iso-8859-1");
			} catch (UnsupportedEncodingException e1) {
				e1.printStackTrace();
			}
			fileNameDisplay = fileNameDisplay.replace(" ", "%20");
			response.addHeader("Content-Disposition", "attachment;filename="
					+ fileNameDisplay + ".xls");
			
			OutputStream fout = null;
			try {
				fout = response.getOutputStream();
				wb.write(response.getOutputStream());
				fout.flush();
				fout.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		//ExcelUtil.toExcel(list, "物料编码:wlCode┋物料名称:wlName┋基本单位入库总数:rkCountJquantity┋常用单位入库总数:rkCountQuantity┋物料单价:price┋总金额:sumMoney","采购汇总报表",response);
	}
}
